﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
namespace DAL
{
    public class ManageDataStoreDAL
    {
        DBConnection cn = new DBConnection();

        public DataTable ThongKeChiTiet(string from, string to)
        {
            return cn.loadData("select pn.productName as [Sản phẩm],sum(pu.numOfProduct) as [Bán được],sum(pu.productTotal) as [Doanh thu] from Invoice i,Purchasing pu,Product p,ProductName pn where i.paymentDate<='" + to + "' and i.paymentDate>='" + from + "' and pu.invoiceId=i.invoiceId and pu.productId=p.productId and p.proNameId=pn.proNameId group by i.paymentDate,pn.productName");
        }

        public DataTable ThongKeTongHopTheoThang(string from, string to)
        {
            string query = "select month(i.paymentDate) as [Tháng],sum(i.total) as [Doanh Thu] from Invoice i where i.paymentDate>='" + from + "' and i.paymentDate<'" + to + "' group by MONTH(i.paymentDate)";
            return cn.loadData(query);
        }

        public DataTable ThongKeTongHopTheoNgay(string from, string to)
        {
            return cn.loadData("select i.paymentDate as [Ngày],sum(i.total) as [Doanh thu] from Invoice i where i.paymentDate<='" + to + "'and i.paymentDate>='" + from + "' group by i.paymentDate");
        }

        public DataTable ThongKeThuChi(string fromMonth, string toMonth)
        {
            return cn.loadData("select c.[Tháng],ISNULL(c.[Chi],0) as [Chi],ISNULL(t.[Thu],0) as [Thu] from (select MONTH(b.importedDate) as [Tháng],sum(b.totalSpending) as[Chi] from Batch b group by MONTH(b.importedDate)) c full outer join (select MONTH(i.paymentDate) as [Tháng],sum(i.total) as[Thu] from Invoice i group by month(i.paymentDate)) t on c.[Tháng]=t.[Tháng] where c.Tháng>=" + fromMonth + " and c.Tháng<=" + toMonth);
        }

    }
}
